CREATE OR REPLACE FUNCTION mirror_uploadable_keys()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.CONSENT_TO_FEDERATION = TRUE AND NEW.SUBMISSION_TYPE = 'SUBMISSION_TYPE_PCR_TEST' ) THEN
        INSERT INTO federation_upload_key
            (KEY_DATA, ROLLING_START_INTERVAL_NUMBER, ROLLING_PERIOD, SUBMISSION_TIMESTAMP, TRANSMISSION_RISK_LEVEL, ORIGIN_COUNTRY, VISITED_COUNTRIES, REPORT_TYPE, DAYS_SINCE_ONSET_OF_SYMPTOMS, CONSENT_TO_FEDERATION, BATCH_TAG, SUBMISSION_TYPE)
            VALUES (NEW.KEY_DATA, NEW.ROLLING_START_INTERVAL_NUMBER, NEW.ROLLING_PERIOD, NEW.SUBMISSION_TIMESTAMP, NEW.TRANSMISSION_RISK_LEVEL, NEW.ORIGIN_COUNTRY, NEW.VISITED_COUNTRIES, NEW.REPORT_TYPE, NEW.DAYS_SINCE_ONSET_OF_SYMPTOMS, NEW.CONSENT_TO_FEDERATION, NULL, NEW.SUBMISSION_TYPE);
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION remove_expired_uploadable_keys()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM federation_upload_key
        WHERE key_data = OLD.key_data
        AND submission_type = OLD.submission_type;
    RETURN OLD;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION mirror_uploadable_swiss_keys()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.CONSENT_TO_FEDERATION = TRUE AND NEW.SUBMISSION_TYPE = 'SUBMISSION_TYPE_PCR_TEST' ) THEN
        INSERT INTO chgs_upload_key
            (KEY_DATA, ROLLING_START_INTERVAL_NUMBER, ROLLING_PERIOD, SUBMISSION_TIMESTAMP, TRANSMISSION_RISK_LEVEL, ORIGIN_COUNTRY, VISITED_COUNTRIES, REPORT_TYPE, DAYS_SINCE_ONSET_OF_SYMPTOMS, CONSENT_TO_FEDERATION, BATCH_TAG, SUBMISSION_TYPE)
            VALUES (NEW.KEY_DATA, NEW.ROLLING_START_INTERVAL_NUMBER, NEW.ROLLING_PERIOD, NEW.SUBMISSION_TIMESTAMP, NEW.TRANSMISSION_RISK_LEVEL, NEW.ORIGIN_COUNTRY, NEW.VISITED_COUNTRIES, NEW.REPORT_TYPE, NEW.DAYS_SINCE_ONSET_OF_SYMPTOMS, NEW.CONSENT_TO_FEDERATION, NULL, NEW.SUBMISSION_TYPE);
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION remove_expired_swiss_uploadable_keys()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM chgs_upload_key
        WHERE key_data = OLD.key_data
        AND submission_type = OLD.submission_type;
    RETURN OLD;
END;
$$
LANGUAGE plpgsql;